﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace BookClubIt.Data.Commands
{
    public class MemberCommandList
    {
        public static SqlCommand SaveMember
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Id", SqlDbType.Int);
                command.Parameters.Add("@Username", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Email", SqlDbType.VarChar, 100);
                command.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
                command.Parameters.Add("@Biography", SqlDbType.VarChar);
                command.Parameters.Add("@Zipcode", SqlDbType.VarChar, 10);
                command.Parameters.Add("@AvatarURL", SqlDbType.VarChar, 1000);
                command.Parameters.Add("@NotificationId", SqlDbType.Int);
                command.Parameters["@Id"].Direction = ParameterDirection.InputOutput;
                command.CommandType = CommandType.Text;
                command.CommandText = @"

                if(not exists(select 1 from Member where id = @Id))
                begin
                    INSERT INTO [Member]
                           ([Status_Id]
                           ,[Email]
                           ,[Username]
                           ,[IP_Address]
                           ,[Created_Date]
                           ,[Biography]
                           ,[Zipcode]
                           ,[AvatarURL]
                            ,[Notification_Id])
                     VALUES
                           (0
                           ,@Email
                           ,@Username
                           ,''
                           ,@CreatedDate
                           ,@Biography
                           ,@Zipcode
                           ,@AvatarURL
                           ,@NotificationId)

                    set @Id = scope_identity()
                end
                else
                begin
                    UPDATE [Member]
                       SET [Status_Id] = 0
                          ,[Email] = @Email
                          ,[Username] = @Username
                          ,[IP_Address] = ''
                          ,[Created_Date] = @CreatedDate
                          ,[Biography] = @Biography
                          ,[Zipcode] = @Zipcode
                          ,[AvatarURL] = @AvatarURL
                          ,[Notification_Id] = @NotificationId
                     WHERE id = @Id
                end
                ";
                return command;
            }    
        }


        public static SqlCommand UpdateMemberNotification
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@UserId", SqlDbType.Int);
                command.Parameters.Add("@NotificationId", SqlDbType.Int);
                command.CommandType = CommandType.Text;
                command.CommandText = @"
                       UPDATE [Member]
                       SET [Notification_Id] = @NotificationId
                       WHERE id = @UserId";
                return command;
            }
        }

        public static SqlCommand GetBookClubMemberCount
        {
            get
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Id", SqlDbType.Int);
                command.CommandType = CommandType.Text;
                command.CommandText = @"
                       select COUNT(*) as numMembers FROM Book_Club 
                        JOIN Member_Book_Club ON Book_Club.Id = Member_Book_Club.Book_Club_Id
                        WHERE Book_Club.Id = @Id";
                return command;
            }
        }


    }

}
